﻿#This powershell script installs the database objects on [dqaqs] database
#
#To run this script to run you should set powershell executionpolicy to 
#remote signed. 
#Open a ps window as admin and type: set-executionpolicy RemoteSigned
#You should be good to go then.

$instance = "localhost"
$database = "dqaqs"
$tempFile = "$([System.Guid]::NewGuid()).sql"
$masterConnStr = "Data Source=$instance;Initial Catalog=master; Integrated Security=SSPI"
$connStr = "Data Source=$instance;Initial Catalog=$database; Integrated Security=SSPI"


$filesInOrder = @"
dbo.Projects.Table.sql,
dbo.DataSources.Table.sql,
dbo.ColumnCollection.UserDefinedTableType.sql,
dbo.CreateTable.StoredProcedure.sql,
dbo.DropTable.StoredProcedure.sql,
dbo.InsertBulk.StoredProcedure.sql,
"@

$redoDb = @"
if (EXISTS(SELECT * FROM sys.Databases where name='$database'))
BEGIN
    alter database [$database] set single_user with ROLLBACK IMMEDIATE
	ALTER DATABASE [$database] SET OFFLINE
	ALTER DATABASE [$database] SET ONLINE
    alter database [$database] set multi_user
	DROP DATABASE [$database]
END;

CREATE DATABASE [$database];

ALTER DATABASE [dqaqs] SET ANSI_NULL_DEFAULT ON WITH NO_WAIT;

ALTER DATABASE [dqaqs] SET ANSI_PADDING ON WITH NO_WAIT;

ALTER DATABASE [dqaqs] SET QUOTED_IDENTIFIER ON WITH NO_WAIT;

SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
"@

function Execute-Command([string[]] $sql, [string] $cs = $connStr)
{
    try
    {
        #sc $sql -Path $tempFile -Force         
        #sqlcmd -s "$instance" -d "$db" -i "$tempFile" -b
        #$rv = $?
        #gc $tempFile
        #del $tempFile -Force
        #if (!$rv)
        #{
        #    throw "Error executing query: $sql"
        #}
        $conn = New-Object System.Data.SqlClient.SqlConnection($cs)
        $conn.Open()
        $sql | %{
            $com = $conn.CreateCommand()
            $com.CommandText = $_
            write-host "RUNNING{ $($_.Substring(0, [Math]::Min($_.Length-2, 80))) }"
            $rv = $com.ExecuteNonQuery()
        }
        $conn.Close()
        $conn.Dispose()
    }
    catch
    {
        Write-Output "Could not execute: $sql"
        throw
    }
}

$files = $filesInOrder -replace "\r","" -replace "\n","" -split "," | where {$_ -match "\.sql"}

try
{
    Execute-Command @($redoDb) $masterConnStr
    Write-Output "Database $database was re-created"
    $files | % {
        Write-Output "Executing $_"
        $f = gc $_
        $fs = $f -replace "\[Dqaqs\]", "[$database]" -join [Environment]::NewLine
        $fs = $fs -split "\b\s*GO\s*\b" 
        $fs = $fs | where { -not ($_ -match "^\s*USE.*$") } | where { $_ -notmatch "^\s*$" } | where {$_.Length -gt 2}
        Execute-Command $fs
    }
}
catch
{
    Write-Error "\r\nErrorHappened: $_"
}